package com.gy.boot.rryExcel;

import cn.hutool.core.util.NumberUtil;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.sax.handler.RowHandler;
import com.gy.boot.common.exception.SysException;
import com.gy.boot.common.util.BlankUtil;
import com.gy.boot.common.util.CommonUtil;
import org.junit.Test;

import java.util.List;
import java.util.Map;

/** 导入社保公积金档位_组装SQL */
public class ImportSocialFundTmpl {

    private static final String[] titleName = {"省份", "城市"
            , "档位名称", "社保增员类型", "社保减员类型", "社保增员截止时间", "社保减员截止时间"
            , "养老_下限", "养老_上限", "养老_公司比例", "养老_个人比例", "养老_是否参与补缴", "养老_最大允许补缴月数"
            , "医疗_下限", "医疗_上限", "医疗_公司比例", "医疗_个人比例", "医疗_是否参与补缴", "医疗_最大允许补缴月数"
            , "大病医疗_计算方式", "大病医疗_下限", "大病医疗_上限", "大病医疗_公司比例", "大病医疗_个人比例", "大病医疗_是否参与补缴", "大病医疗_最大允许补缴月数"
            , "补充医疗_计算方式", "补充医疗_下限", "补充医疗_上限", "补充医疗_公司比例", "补充医疗_个人比例", "补充医疗_是否参与补缴", "补充医疗_最大允许补缴月数"
            , "失业_下限", "失业_上限", "失业_公司比例", "失业_个人比例", "失业_是否参与补缴", "失业_最大允许补缴月数"
            , "工伤_下限", "工伤_上限", "工伤_公司比例", "工伤_是否参与补缴", "工伤_最大允许补缴月数"
            , "生育_下限", "生育_上限", "生育_公司比例"

            , "公积金_增员类型", "公积金_减员类型", "公积金_增员截止时间", "公积金_减员截止时间"
            , "公积金_基数下限", "公积金_基数上限", "公积金_公司比例", "公积金_个人比例"
            , "公积金_档位备注", "公积金_是否补缴", "公积金_最大允许补缴月数"
    };

    private static final String[] titleCode = {"province", "city"
            , "name", "addstaff_type", "minusstaff_type", "add_endtime", "minus_endtime"
            , "ei_low", "ei_high", "ei_company_ratio", "ei_person_ratio", "ei_back", "ei_back_month"
            , "medicare_low", "medicare_high", "medicare_company_ratio", "medicare_person_ratio", "medicare_back", "medicare_back_month"
            , "lmt_calculate", "lmt_low", "lmt_heigh", "lmt_company_ratio", "lmt_person_ratio", "lmt_back", "lmt_back_month"
            , "es_calculate", "es_low", "es_heigh", "es_company_ratio", "es_person_ratio", "es_back", "es_back_month"
            , "ui_low", "ui_high", "ui_company_ratio", "ui_person_ratio", "ui_back", "ui_back_month"
            , "oi_low", "oi_heigh", "oi_company_ratio", "oi_back", "oi_back_month"
            , "mi_low", "mi_high", "mi_company_ratio"

            , "fund_addstaff_type", "fund_minusstaff_type", "fund_add_endtime", "fund_minus_endtime"
            , "fund_low", "fund_high", "company_ratio", "person_ratio"
            , "fund_name", "fund_back", "fund_back_month"
    };

    @Test
    public void testImpl() {
        StringBuilder fundSql = new StringBuilder();
        StringBuilder socialSql = new StringBuilder();
        Map<String, Integer> keyIndex = CommonUtil.getKeyIndex(titleCode);
        String path = "C:\\Users\\asus\\Documents\\WeChat Files\\wxid_19q8x5qctowv22\\FileStorage\\File\\2023-03\\全国社保政策录入模板.xlsx";
        ExcelUtil.readBySax(path, 0, readRow(socialSql, fundSql, keyIndex));
        System.out.println(socialSql);
        System.out.println(fundSql);
    }
    /** 2022-11-11 于晓 杭州钱江 员工合同 表处理 */
    private RowHandler readRow(StringBuilder socialSql, StringBuilder fundSql, Map<String, Integer> keyIndex) {
        final int[] readRowIndex = {0};
        System.out.println("readRowIndex[0]: " + readRowIndex[0]);
        return (sheetIndex, rowIndex, rowList) -> {
            if (rowIndex < 2 && readRowIndex[0] < 2) {
                readRowIndex[0] += 1;  // 防止二次读取表头数据
                return;
            }
            String city = CommonUtil.getValue(rowList, keyIndex.get("city"));
            String province = CommonUtil.getValue(rowList, keyIndex.get("province"));
            if (BlankUtil.isBlankNull(province) || BlankUtil.isBlankNull(city)) {
                return;
            }
            addSocialSql(socialSql, keyIndex, rowList);
            addFundSql(fundSql, keyIndex, rowList);
        };
    }


    private void addFundSql(StringBuilder fundSql, Map<String, Integer> keyIndex, List<Object> rowList) {
        String fund_name = CommonUtil.getValue(rowList, keyIndex.get("fund_name"));
        if (BlankUtil.isBlankNull(fund_name)) {
            return;
        }
        String addstaff_type = CommonUtil.getValue(rowList, keyIndex.get("fund_addstaff_type"));
        addstaff_type = getAddStaffType(addstaff_type);
        String minusstaff_type = CommonUtil.getValue(rowList, keyIndex.get("fund_minusstaff_type"));
        minusstaff_type = getMinStaffType(minusstaff_type);

        String add_endtime = CommonUtil.getValue(rowList, keyIndex.get("fund_add_endtime"));
        String minus_endtime = CommonUtil.getValue(rowList, keyIndex.get("fund_minus_endtime"));

        String fund_low = CommonUtil.getValue(rowList, keyIndex.get("fund_low"));
        String fund_high = CommonUtil.getValue(rowList, keyIndex.get("fund_high"));
        String company_ratio = CommonUtil.getValue(rowList, keyIndex.get("company_ratio"));
        String person_ratio = CommonUtil.getValue(rowList, keyIndex.get("person_ratio"));
        String fund_back = CommonUtil.getValue(rowList, keyIndex.get("fund_back"));
        fund_back = getBack(fund_back);
        String fund_back_month = CommonUtil.getValue(rowList, keyIndex.get("fund_back_month"));

        String sql = "INSERT INTO te_fund_gears(benefit_id, addstaff_type, minusstaff_type, add_endtime, minus_endtime" +
                ", fund_low, fund_high, company_ratio, person_ratio, fund_name, fund_back, fund_back_month) " +
                "VALUES (3, "+addstaff_type+", "+minusstaff_type+", "+add_endtime+", "+minus_endtime+
                ", "+fund_low+", "+fund_high+", "+company_ratio+", "+person_ratio+", '"+fund_name+"', "+fund_back+", "+fund_back_month+");\n";
        fundSql.append(sql);
    }

    private void addSocialSql(StringBuilder socialSql, Map<String, Integer> keyIndex, List<Object> rowList) {
        String name = CommonUtil.getValue(rowList, keyIndex.get("name"));
        if (BlankUtil.isBlankNull(name)) {
            return;
        }
        String addstaff_type = CommonUtil.getValue(rowList, keyIndex.get("addstaff_type"));
        addstaff_type = getAddStaffType(addstaff_type);

        String minusstaff_type = CommonUtil.getValue(rowList, keyIndex.get("minusstaff_type"));
        minusstaff_type = getMinStaffType(minusstaff_type);

        String add_endtime = CommonUtil.getValue(rowList, keyIndex.get("add_endtime"));
        String minus_endtime = CommonUtil.getValue(rowList, keyIndex.get("minus_endtime"));

        String ei_low = CommonUtil.getValue(rowList, keyIndex.get("ei_low"));
        String ei_high = CommonUtil.getValue(rowList, keyIndex.get("ei_high"));
        String ei_company_ratio = CommonUtil.getValue(rowList, keyIndex.get("ei_company_ratio"));
        String ei_person_ratio = CommonUtil.getValue(rowList, keyIndex.get("ei_person_ratio"));
        String ei_back = CommonUtil.getValue(rowList, keyIndex.get("ei_back"));
        ei_back = getBack(ei_back);
        String ei_back_month = CommonUtil.getValue(rowList, keyIndex.get("ei_back_month"));

        String medicare_low = CommonUtil.getValue(rowList, keyIndex.get("medicare_low"));
        String medicare_high = CommonUtil.getValue(rowList, keyIndex.get("medicare_high"));
        String medicare_company_ratio = CommonUtil.getValue(rowList, keyIndex.get("medicare_company_ratio"));
        String medicare_person_ratio = CommonUtil.getValue(rowList, keyIndex.get("medicare_person_ratio"));
        String medicare_back = CommonUtil.getValue(rowList, keyIndex.get("medicare_back"));
        medicare_back = getBack(medicare_back);
        String medicare_back_month = CommonUtil.getValue(rowList, keyIndex.get("medicare_back_month"));

        String lmt_calculate = CommonUtil.getValue(rowList, keyIndex.get("lmt_calculate"));
        lmt_calculate = getCalculate(lmt_calculate);
        String lmt_low = CommonUtil.getValue(rowList, keyIndex.get("lmt_low"));
        String lmt_heigh = CommonUtil.getValue(rowList, keyIndex.get("lmt_heigh"));
        String lmt_company_ratio = CommonUtil.getValue(rowList, keyIndex.get("lmt_company_ratio"));
        String lmt_person_ratio = CommonUtil.getValue(rowList, keyIndex.get("lmt_person_ratio"));
        String lmt_back = CommonUtil.getValue(rowList, keyIndex.get("lmt_back"));
        lmt_back = getBack(lmt_back);
        String lmt_back_month = CommonUtil.getValue(rowList, keyIndex.get("lmt_back_month"));

        String es_calculate = CommonUtil.getValue(rowList, keyIndex.get("es_calculate"));
        es_calculate = getCalculate(es_calculate);
        String es_low = CommonUtil.getValue(rowList, keyIndex.get("es_low"));
        String es_heigh = CommonUtil.getValue(rowList, keyIndex.get("es_heigh"));
        String es_company_ratio = CommonUtil.getValue(rowList, keyIndex.get("es_company_ratio"));
        String es_person_ratio = CommonUtil.getValue(rowList, keyIndex.get("es_person_ratio"));
        String es_back = CommonUtil.getValue(rowList, keyIndex.get("es_back"));
        es_back = getBack(es_back);
        String es_back_month = CommonUtil.getValue(rowList, keyIndex.get("es_back_month"));

        String ui_low = CommonUtil.getValue(rowList, keyIndex.get("ui_low"));
        String ui_high = CommonUtil.getValue(rowList, keyIndex.get("ui_high"));
        String ui_company_ratio = CommonUtil.getValue(rowList, keyIndex.get("ui_company_ratio"));
        String ui_person_ratio = CommonUtil.getValue(rowList, keyIndex.get("ui_person_ratio"));
        String ui_back = CommonUtil.getValue(rowList, keyIndex.get("ui_back"));
        ui_back = getBack(ui_back);
        String ui_back_month = CommonUtil.getValue(rowList, keyIndex.get("ui_back_month"));

        String oi_low = CommonUtil.getValue(rowList, keyIndex.get("oi_low"));
        String oi_heigh = CommonUtil.getValue(rowList, keyIndex.get("oi_heigh"));
        String oi_company_ratio = CommonUtil.getValue(rowList, keyIndex.get("oi_company_ratio"));
        String oi_back = CommonUtil.getValue(rowList, keyIndex.get("oi_back"));
        oi_back = getBack(oi_back);
        String oi_back_month = CommonUtil.getValue(rowList, keyIndex.get("oi_back_month"));

        String mi_low = CommonUtil.getValue(rowList, keyIndex.get("mi_low"));
        String mi_high = CommonUtil.getValue(rowList, keyIndex.get("mi_high"));
        String mi_company_ratio = CommonUtil.getValue(rowList, keyIndex.get("mi_company_ratio"));

        int is_independent_base = 1; // 0是统一基数, 1是独立基数
        int is_independent_back = 1; // 0是统一补缴, 1是独立补缴
        // 组装SQL
        String sql = "INSERT INTO te_social_security(benefit_id, name, addstaff_type, minusstaff_type, add_endtime, minus_endtime" +
                ", ei_low, ei_high, ei_company_ratio, ei_person_ratio, ei_back, ei_back_month" +
                ", medicare_low, medicare_high, medicare_company_ratio, medicare_person_ratio, medicare_back, medicare_back_month" +
                ", lmt_calculate, lmt_low, lmt_heigh, lmt_company_ratio, lmt_person_ratio, lmt_back, lmt_back_month" +
                ", es_calculate, es_low, es_heigh, es_company_ratio, es_person_ratio, es_back, es_back_month" +
                ", ui_low, ui_high, ui_company_ratio, ui_person_ratio, ui_back, ui_back_month" +
                ", oi_low, oi_heigh, oi_company_ratio, oi_back, oi_back_month" +
                ", mi_low, mi_high, mi_company_ratio, is_independent_base, is_independent_back) " +
                "VALUES (3, '"+name+"', "+addstaff_type+", "+minusstaff_type+", "+add_endtime+", "+minus_endtime +
                ", "+ei_low+", "+ei_high+", "+ei_company_ratio+", "+ei_person_ratio+", "+ei_back+", "+ei_back_month +
                ", "+medicare_low+", "+medicare_high+", "+medicare_company_ratio+", "+medicare_person_ratio+", "+medicare_back+", "+medicare_back_month +
                ", "+lmt_calculate+", "+lmt_low+", "+lmt_heigh+", "+lmt_company_ratio+", "+lmt_person_ratio+", "+lmt_back+", "+lmt_back_month +
                ", "+es_calculate+", "+es_low+", "+es_heigh+", "+es_company_ratio+", "+es_person_ratio+", "+es_back+", "+es_back_month +
                ", "+ui_low+", "+ui_high+", "+ui_company_ratio+", "+ui_person_ratio+", "+ui_back+", "+ui_back_month +
                ", "+oi_low+", "+oi_heigh+", "+oi_company_ratio+", "+oi_back+", "+oi_back_month +
                ", "+mi_low+", "+mi_high+", "+mi_company_ratio+", "+is_independent_base+", "+is_independent_back+");\n";
        socialSql.append(sql);
    }

    /** 是否参与补缴 */
    private String getBack(String ei_back) {
        return "是".equals(ei_back) ? "1" : "0";
    }
    /** 增员类型 */
    private static String getAddStaffType(String add_type_name) {
        if ("当月增当月".equals(add_type_name)) { return "11"; }
        if ("当月增下月".equals(add_type_name)) { return "12"; }
        throw new RuntimeException("增员类型-参数错误: " + add_type_name);
    }
    /** 减员类型 */
    private static String getMinStaffType(String min_type_name) {
        if ("当月减当月".equals(min_type_name)) { return "11"; }
        if ("当月减下月".equals(min_type_name)) { return "12"; }
        throw new RuntimeException("减员类型-参数错误: " + min_type_name);
    }
    /** 计算类型 */
    private static String getCalculate(String calculate_name) {
        if (BlankUtil.isBlank(calculate_name)) { return "1"; }
        if ("基数计算".equals(calculate_name)) { return "1"; }
        if ("年固定值".equals(calculate_name)) { return "2"; }
        if ("月固定值".equals(calculate_name)) { return "3"; }
        throw new RuntimeException("计算类型-参数错误: " + calculate_name);
    }


// SELECT GROUP_CONCAT(social_security_id) AS social_security_ids FROM te_social_security WHERE benefit_id = 3 AND DATE_FORMAT(create_time, '%Y-%m-%d %H') = '2023-02-08 09';


    private static final String SQL_Tmpl = "INSERT INTO te_social_security_expand (social_security_id, is_ei, is_medicare, is_lmt, is_es, is_ui, is_oi, is_mi) VALUES (s_id, 1, 1, 1, 1, 1, 1, 1);";
    /** 设置开启险种SQL */
    @Test
    public void testExpand() {
        String social_security_ids = "959,960,961,962";
        String[] idArr = social_security_ids.split(",");
        StringBuilder SQL_SB = new StringBuilder();
        for (String id : idArr) {
            String sql = SQL_Tmpl.replaceAll("s_id", id);
            SQL_SB.append(sql).append("\n");

        }
        System.out.println(SQL_SB);
    }













    /**  修改社保比例   */
    @Test
    public void testImplUpdate() {
        StringBuilder socialUpdateSql = new StringBuilder();
        Map<String, Integer> keyIndex = CommonUtil.getKeyIndex(titleNameByUpdate);
        String path = "C:\\Users\\asus\\Documents\\WeChat Files\\wxid_19q8x5qctowv22\\FileStorage\\File\\2023-03\\全国社保政策录入模板.xlsx";
        ExcelUtil.readBySax(path, 0, readRowUpdate(socialUpdateSql, keyIndex));
        System.out.println(socialUpdateSql);
    }
    private RowHandler readRowUpdate(StringBuilder socialUpdateSql, Map<String, Integer> keyIndex) {
        final int[] readRowIndex = {0};
        System.out.println("readRowIndex[0]: " + readRowIndex[0]);
        return (sheetIndex, rowIndex, rowList) -> {
            if (rowIndex < 1 && readRowIndex[0] < 1) {
                checkTitleNameByUpdate(rowList);
                readRowIndex[0] += 1;  // 防止二次读取表头数据
                return;
            }
            String city = CommonUtil.getValue(rowList, keyIndex.get("城市"));
            String name = CommonUtil.getValue(rowList, keyIndex.get("档位名称"));
            if (BlankUtil.isBlankNull(name) || BlankUtil.isBlankNull(city) || !"南昌市".equals(city)) {
                return;
            }
            String company_ratio = CommonUtil.getValue(rowList, keyIndex.get("修改为配置比例"));
            if (BlankUtil.isBlankNull(company_ratio)) { return; }
            String keyName = CommonUtil.getValue(rowList, keyIndex.get("修改内容"));
            String key = getKeyByKeyName(keyName);
            if (BlankUtil.isBlankNull(key)) { return; }
            company_ratio = company_ratio.replaceAll("%", "");
            company_ratio = NumberUtil.round(NumberUtil.mul(company_ratio, "100"), 4).toPlainString();
            //  UPDATE te_social_security SET oi_company_ratio=0.45 WHERE benefit_id=3 AND is_history=0 AND name='';
            socialUpdateSql.append("UPDATE te_social_security SET ")
                    .append(key).append("_company_ratio=")
                    .append(company_ratio)
                    .append(" WHERE benefit_id=3 AND is_history=0 AND name='").append(name).append("';\n");
        };
    }

    /** ei-养老，rg-残保金，mi-生育，ui-失业，oi-工伤，es-医疗补充，lmt-大病医疗，medicare-医疗，fund-公积金 */
    private String getKeyByKeyName(String keyName) {
        if (BlankUtil.isBlankNull(keyName)) { return null; }
        if (keyName.contains("养老")) { return "ei"; }
        if (keyName.contains("生育")) { return "mi"; }
        if (keyName.contains("失业")) { return "ui"; }
        if (keyName.contains("工伤")) { return "oi"; }
        if (keyName.contains("医疗补充")) { return "es"; }
        if (keyName.contains("大病医疗")) { return "lmt"; }
        if (keyName.contains("医疗")) { return "medicare"; }
        if (keyName.contains("残保金")) { return "rg"; }
        if (keyName.contains("公积金")) { return "fund"; }
        return null;
    }
    private static final String[] titleNameByUpdate = {"序号","城市","档位名称","修改内容","原比例","修改为配置比例"};
    private void checkTitleNameByUpdate(List<Object> rowList) {
        for (int i = 0; i < titleNameByUpdate.length; i++) {
            String title = CommonUtil.getValue(rowList, i);
            if (!titleNameByUpdate[i].equals(title)) {
                throw new SysException("修改社保比例模板错误");
            }
        }
    }


}
